﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Transactions;
using System.Data.Objects;
using System.Data.Entity.Infrastructure;

namespace Tema2_Adabd.DataLayer
{
    public partial class AccesBazaDeDate
    {
        private string numeUtilizator;

        internal string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            }
        }

        public string hashulParolei(string numeUser)
        {
            string result = null;

            using (var db = new Tema2Entities1())
            {
                result = db.daParola(numeUser).First();
            }

            return result;

        }

        public void recordOperation(string numeUtilizator,string obiect,string tipOperatie,
            DateTime dataEfectuariiOperatiei)
        {
            using (var db = new Tema2Entities1())
            {
                db.recordOperation(numeUtilizator,obiect,tipOperatie,dataEfectuariiOperatiei);
            }
        }

        public bool numeExistent(string nume)
        {
            int count;
            using (var db = new Tema2Entities1())
            {
                count = (from o in db.Utilizatoris
                             where o.nume_utilizator == nume
                             select o).Count();
            }

            if (count > 0)
                return true;
            else
                return false;
        }

        public List<string> autentificaUtilizator(string numeUtilizator,string parola)
        {
            List<string> permisiuni = new List<string>();

            using (var db = new Tema2Entities1())
            {
                var results = db.autentificaUtilizator(numeUtilizator, parola).ToList();
                foreach (var r in results)
                    permisiuni.Add(r);
            }

            return permisiuni;
        }

        public int userID(string nume)
        {
            int id;
            using (var db = new Tema2Entities1())
            {
                id = (int)db.daUserID(nume).First();
            }

            return id;
        }

        public int permisiuneID(string permisiune)
        {
            int idPermisiune;
            using (var db = new Tema2Entities1())
            {
                idPermisiune = (from Permisiune in db.Permisiunis
                                    where Permisiune.permisiune==permisiune
                                    select Permisiune.id_permisiune).FirstOrDefault();
            }

            return idPermisiune;
        }

        public void adaugaPermisiuni(int idPermisiune,int idUser)
        {
            using (var db = new Tema2Entities1())
            {
                db.Add_util_perm(idUser,idPermisiune);
            }
        }

        public void creeazaUtilizator(string nume, string parola,string creator)
        {
            using (var db = new Tema2Entities1())
            {
                db.adaugaUtilizator(nume,parola,creator);
            }
        }

        public void stergeUtilizator(string numeUtilizator,string admin)
        {
            using (var db = new Tema2Entities1())
            {
                db.stergeUtilizator(numeUtilizator,admin);
            }
        }

        public bool produsExistentInBazaDeDate(string numeProdus)
        {
            int count = 0;
            using (var db = new Tema2Entities1())
            {
                count = (from Produse in db.Produses
                         where Produse.nume_produs == numeProdus && Produse.disabled_==false
                         select Produse.id_produs).Count();
            }

            if (count != 0)
                return true;
            else
                return false;
        }

        
        public void daPozaProdus(string queryPoza, SqlParameter par)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection())
                {
                    connection.ConnectionString = ConnectionString;

                    // Assumes that connection is a valid SqlConnection object.

                    SqlCommand command = new SqlCommand(
                      queryPoza, connection);


                    command.Parameters.Add(par);

                    // Writes the BLOB to a file (*.bmp).
                    FileStream stream;
                    // Streams the BLOB to the FileStream object.
                    BinaryWriter writer;

                    // Size of the BLOB buffer.
                    int bufferSize = 100;
                    // The BLOB byte[] buffer to be filled by GetBytes.
                    byte[] outByte = new byte[bufferSize];
                    // The bytes returned from GetBytes.
                    long retval;
                    // The starting position in the BLOB output.
                    long startIndex = 0;

                    // The publisher id to use in the file name.
                    string pubID = "";

                    // Open the connection and read data into the DataReader.
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                    while (reader.Read())
                    {
                        // Get the publisher id, which must occur before getting the logo.
                        pubID = reader.GetString(0);

                        // Create a file to hold the output.
                        stream = new FileStream(
                          "logo" + pubID + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
                        writer = new BinaryWriter(stream);

                        // Reset the starting byte for the new BLOB.
                        startIndex = 0;

                        // Read bytes into outByte[] and retain the number of bytes returned.
                        retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);

                        // Continue while there are bytes beyond the size of the buffer.
                        while (retval == bufferSize)
                        {
                            writer.Write(outByte);
                            writer.Flush();

                            // Reposition start index to end of last buffer and fill buffer.
                            startIndex += bufferSize;
                            retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
                        }

                        // Write the remaining buffer.
                        writer.Write(outByte, 0, (int)retval - 1);
                        writer.Flush();

                        // Close the output file.
                        writer.Close();
                        stream.Close();
                    }

                    // Close the reader.
                    reader.Close();

                }
                ts.Complete();
            }
        }
    }

}

